Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Optimization Approaches

The following hints allow you to indicate to Oracle to use a certain optimization approach, regardless of what optimization approach has been specified in the Oracle initialization file. The indicated approach takes precedence over any other specification of the optimization approach.

ALL_ROWS

The ALL_ROWS hint specifies that the cost-based optimizer should be used with the goal of best throughput. This method creates an execution plan with the least amount of total resource consumption and best throughput. The cost-based optimization approach is used, regardless of the presence or absence of statistics.

The syntax of this hint is as follows:

/*+ ALL_ROWS */

If you include hints for access paths or join operations with the ALL_ROWS hint, the optimizer gives precedence to the access paths and join hints.

If no statistics are available for the tables involved, and a hint forces the cost-based optimizer, the optimizer uses default statistics to determine the most optimal execution plan. These default statistics include allocated storage and number of data blocks used. These statistics are not very good, but they are better than no statistics at all. If you intend to use the cost-based optimizer, you should run the ANALYZE command on the tables, indexes, and clusters to be accessed to get better statistics.

CHOOSE

The CHOOSE hint causes the optimizer to choose the rule-based or cost-based optimization approach based on the presence of statistics available on the tables being accessed by the SQL statements. If statistics are available for the tables being accessed, the optimizer chooses the cost-based approach, with the goal of best throughput. If no statistics exist for the tables being accessed, the rule-based optimizer is used.

The syntax of this hint is as follows:

/*+ CHOOSE */

FIRST_ROWS

The FIRST_ROWS hint specifies that the cost-based optimizer should be used with the goal of best response time. This method creates an execution plan with the least amount of resource consumption to return the first row. The cost-based optimization approach is used, regardless of the presence or absence of statistics.

The syntax of this hint is as follows:

/*+ FIRST_ROWS */

When you use the FIRST_ROWS hint, the optimizer also makes the following choices:

  If an index scan is available, it may be chosen over a full-table scan.
  If an index scan is available, the optimizer may choose a nested-loops join over a sort-merge join whenever the associated table is the potential inner table of the nested loops.
  If an index scan is available through an ORDER BY clause, the optimizer may choose it to avoid a sort operation.

The FIRST_ROWS hint is ignored in DELETE and UPDATE statements that contain the following keywords:

  Set operators (UNION, INTERSECT, MINUS, UNION ALL)
  GROUP BY clauses
  FOR UPDATE clauses
  Group or aggregate functions such as AVG, MIN, MAX, COUNT, SUM, STDDEV, and VARIANCE
  DISTINCT operator

These statements cannot be optimized for best response time by retrieving the first row because the operation involved requires that all rows must be retrieved before returning the first row. If you do use the FIRST_ROWS hint in any of these statements, the cost-based optimizer is still used, regardless of the presence of statistics on the tables involved.

If you include hints for access paths or join operations with the FIRST_ROWS hint, the optimizer gives precedence to the access paths and join hints.

If no statistics are available for the tables involved and a hint forces the cost-based optimizer, the optimizer uses default statistics to determine the most optimal execution plan. These default statistics include allocated storage and number of data blocks used. These statistics are not very good, but they are better than no statistics at all. If you intend to use the cost-based optimizer, you should run the ANALYZE command on the tables, indexes, and clusters to be accessed to get better statistics.

RULE

The RULE hint specifies that the rule-based optimization approach should be used. This hint also causes the optimizer to ignore any other hints that may be specified in this statement block.

The syntax of this hint is as follows:

/*+ RULE */

Because the rule-based approach is based simply on the SQL statements themselves, you must have statistics about the database tables. The rule-based approach uses the following steps to determine the execution plan:

1.  Determines possible execution plans.
2.  Ranks the different plans according to a specific ranking (see Table 27.1 in Chapter 27, “Using the Oracle Optimizer”).
3.  Chooses the approach with the lowest ranking.

In this way, the rule-based optimization approach is very efficient and works well. However, if statistics are available for your tables, clusters, or indexes, the cost-based approach can be very efficient.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.